package com.briup.jdbc;

import org.junit.Test;

import java.sql.*;

/**
 * sql注入的问题产生
 *  sql语句是拼接的字符串，可以通过传sql脚本来改变原有的sql语义，从而跳过安全检查，窃取数据
 * sql注入的解决办法
 *  jdbc提供了预编译的语句对象，可以提前设置sql的语法结构
 */
public class Test6 {
    public static final String driver= "com.mysql.cj.jdbc.Driver";
    public static final String url= "jdbc:mysql:///db01";
    public static final String username= "root";
    public static final String password= "rootroot";

    @Test
    public void loginTest(){
        Connection connection = null;
        // Statement statement = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // 准备账号密码用作登陆
        String un = "briup";
        String pwd = "briup";
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            // 设计sql的结构
            String sql = "select count(*) from t_user where name = ? and password = ?";
            // 创建语句对象
            preparedStatement = connection.prepareStatement(sql);
            // 输出最终的sql语句
            System.out.println("sql = [" + sql+";]");
            // 插入数据
            preparedStatement.setString(1,un);
            preparedStatement.setString(2,pwd);
            // 执行查询语句
            resultSet = preparedStatement.executeQuery();
            // 这里不用循环迭代，因为查询结果必只有一个值
            resultSet.next();
            int anInt = resultSet.getInt(1);

            if(anInt == 0){
                System.out.println("登陆失败");
                return;
            }

            System.out.println("登陆成功");

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if(resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(preparedStatement != null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}
